package cn.schoolwow.quickdao.module.database.ddl.query.flow.index;

import cn.schoolwow.quickdao.annotation.IndexType;
import cn.schoolwow.quickdao.domain.QuickDAOConfig;
import cn.schoolwow.quickdao.domain.database.ddl.IndexField;
import cn.schoolwow.quickdao.domain.database.parent.DatabaseType;
import cn.schoolwow.quickdao.domain.entity.Entity;
import cn.schoolwow.quickdao.domain.entity.Property;
import cn.schoolwow.quickdao.module.database.parent.domain.ResultSetConsumer;
import cn.schoolwow.quickdao.module.database.parent.flow.executor.ExecuteQueryConnectionFlow;
import cn.schoolwow.quickflow.domain.FlowContext;
import cn.schoolwow.quickflow.flow.BusinessFlow;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

public class GetEntityIndexListFlow implements BusinessFlow {
    @Override
    public void executeBusinessFlow(FlowContext flowContext) throws Exception {
        DatabaseType databaseType = flowContext.checkInstanceData(DatabaseType.class);

        flowContext.putTemporaryData("name", "获取表索引信息");
        switch (databaseType){
            case H2:{
                getByH2(flowContext);
            }break;
            case SQLite:{
                getBySQLite(flowContext);
            }break;
            case Mysql:
            case MariaDB:{
                getByMysql(flowContext);
            }break;
            case Postgresql:{
                getByPostgres(flowContext);
            }break;
            case SQLServer:{
                getBySQLServer(flowContext);
            }break;
            case Oracle:{
                getByOracle(flowContext);
            }break;
        }
    }

    @Override
    public String name() {
        return "获取数据库字段信息";
    }

    private void getByH2(FlowContext flowContext) {
        QuickDAOConfig quickDAOConfig = flowContext.checkInstanceData(QuickDAOConfig.class);
        List<Entity> entityList = (List<Entity>) flowContext.checkData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("sql", "select table_name, sql from information_schema.indexes where table_schema = '" + quickDAOConfig.databaseContext.databaseName + "'")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("table_name");
                            for (Entity entity : entityList) {
                                if (!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }
                                String sql = resultSet.getString("sql");
                                String[] tokens = sql.split("\"");
                                IndexField indexField = new IndexField();
                                if (tokens[0].contains("UNIQUE")) {
                                    indexField.indexType = IndexType.UNIQUE;
                                } else {
                                    indexField.indexType = IndexType.NORMAL;
                                }
                                indexField.indexName = tokens[3];
                                indexField.tableName = tokens[7];
                                for (int i = 9; i < tokens.length - 1; i++) {
                                    indexField.columns.add(tokens[i]);
                                }
                                entity.indexFieldList.add(indexField);
                            }
                        }
                    }
                })
                .execute();
    }

    private void getBySQLite(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.checkData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("sql", "select tbl_name, sql from sqlite_master where type='index' and sql is not null;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        List<String> warningSQLList = new ArrayList<>();
                        while(resultSet.next()){
                            String tableName = resultSet.getString("tbl_name");
                            for (Entity entity : entityList) {
                                if (!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }

                                String sql = resultSet.getString("sql");
                                if (!sql.contains("\"") && !sql.contains("`")) {
                                    warningSQLList.add(sql);
                                    continue;
                                }
                                String[] tokens = sql.split("[\"|`]");
                                IndexField indexField = new IndexField();
                                if (tokens[0].contains("UNIQUE")) {
                                    indexField.indexType = IndexType.UNIQUE;
                                } else {
                                    indexField.indexType = IndexType.NORMAL;
                                }
                                indexField.indexName = tokens[1];
                                indexField.tableName = tokens[3];
                                for (int i = 5; i < tokens.length - 1; i++) {
                                    indexField.columns.add(tokens[i]);
                                }
                                entity.indexFieldList.add(indexField);
                            }
                        }
                        if(!warningSQLList.isEmpty()){
                            flowContext.putTemporaryData("warningSQLList", warningSQLList);
                            flowContext.remark("存在异常索引!索引不包含\"也不包含`");
                        }
                    }
                })
                .execute();
    }

    private void getByMysql(FlowContext flowContext){
        QuickDAOConfig quickDAOConfig = flowContext.checkInstanceData(QuickDAOConfig.class);
        List<Entity> entityList = (List<Entity>) flowContext.checkData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("sql", "select table_name, index_name, non_unique, column_name, index_type, index_comment from information_schema.`statistics` where table_schema = '"+quickDAOConfig.databaseContext.databaseName+"';")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("table_name");
                            for (Entity entity : entityList) {
                                String indexName = resultSet.getString("index_name");
                                if("PRIMARY".equalsIgnoreCase(indexName)){
                                    for (Property property : entity.properties) {
                                        if (property.column.equals(resultSet.getString("column_name"))) {
                                            property.id = true;
                                        }
                                    }
                                    continue;
                                }
                                if (!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }
                                IndexField indexField = null;
                                for (IndexField indexField1 : entity.indexFieldList) {
                                    if (indexField1.indexName.equals(indexName)) {
                                        indexField = indexField1;
                                        break;
                                    }
                                }
                                if (null == indexField) {
                                    indexField = new IndexField();
                                    indexField.tableName = tableName;
                                    indexField.indexType = resultSet.getInt("non_unique") == 0 ? IndexType.UNIQUE : IndexType.NORMAL;
                                    if ("FULLTEXT".equals(resultSet.getString("index_type"))) {
                                        indexField.indexType = IndexType.FULLTEXT;
                                    }
                                    indexField.indexName = resultSet.getString("index_name");
                                    indexField.using = resultSet.getString("index_type");
                                    indexField.comment = resultSet.getString("index_comment");
                                    entity.indexFieldList.add(indexField);
                                }
                                indexField.columns.add(resultSet.getString("column_name"));
                            }
                        }
                    }
                })
                .execute();
    }

    private void getByPostgres(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.checkData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("sql", "select tablename,indexname,indexdef from pg_indexes;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("tablename");
                            for (Entity entity : entityList) {
                                if (!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }
                                IndexField indexField = new IndexField();
                                indexField.tableName = resultSet.getString("tablename");
                                indexField.indexName = resultSet.getString("indexname");

                                String def = resultSet.getString("indexdef");
                                if (def.contains("UNIQUE INDEX")) {
                                    indexField.indexType = IndexType.UNIQUE;
                                } else {
                                    indexField.indexType = IndexType.NORMAL;
                                }
                                indexField.using = def.substring(def.indexOf("USING") + "USING".length(), def.indexOf("(")).replace("\"", "");
                                String[] columns = def.substring(def.indexOf("(") + 1, def.indexOf(")")).split(",");
                                for (int i = 0; i < columns.length; i++) {
                                    indexField.columns.add(columns[i]);
                                }
                                entity.indexFieldList.add(indexField);
                            }
                        }
                    }
                })
                .execute();
    }

    private void getBySQLServer(FlowContext flowContext) throws InterruptedException {
        List<Entity> entityList = (List<Entity>) flowContext.checkData("entityList");
        ThreadPoolExecutor threadPoolExecutor = (ThreadPoolExecutor) Executors.newFixedThreadPool(10);
        for(Entity entity:entityList){
            threadPoolExecutor.execute(()->{
                flowContext.startFlow(new ExecuteQueryConnectionFlow())
                        .putThreadLocalData("sql", "select i.is_unique,i.name,col.name col_name from sys.indexes i left join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id left join (select * from sys.all_columns where object_id = object_id( ?, N'U' )) col on ic.column_id = col.column_id where i.object_id = object_id(?, N'U' ) and i.index_id > 0;")
                        .putThreadLocalData("parameters", Arrays.asList(entity.tableName,entity.tableName))
                        .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                            @Override
                            public void consumeResultSet(ResultSet resultSet) throws Exception {
                                while (resultSet.next()) {
                                    IndexField indexField = new IndexField();
                                    if (resultSet.getBoolean("is_unique")) {
                                        indexField.indexType = IndexType.UNIQUE;
                                    } else {
                                        indexField.indexType = IndexType.NORMAL;
                                    }
                                    indexField.indexName = resultSet.getString("name");
                                    //判断是否已经存在该索引
                                    IndexField existIndexField = entity.indexFieldList.stream().filter(indexField1 -> indexField1.indexName.equals(indexField.indexName)).findFirst().orElse(null);
                                    if (null != existIndexField) {
                                        existIndexField.columns.add(resultSet.getNString("col_name"));
                                    } else {
                                        indexField.columns.add(resultSet.getNString("col_name"));
                                        entity.indexFieldList.add(indexField);
                                    }
                                }
                            }
                        })
                        .execute();
            });
        }
        threadPoolExecutor.shutdown();
        threadPoolExecutor.awaitTermination(3, TimeUnit.MINUTES);
    }

    private void getByOracle(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.checkData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("sql", "select table_name, index_name,uniqueness from user_indexes")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("table_name");
                            for (Entity entity : entityList) {
                                if (!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }
                                IndexField indexField = new IndexField();
                                indexField.tableName = tableName;
                                if("UNIQUE".equalsIgnoreCase(resultSet.getString("uniqueness"))){
                                    indexField.indexType = IndexType.UNIQUE;
                                }else{
                                    indexField.indexType = IndexType.NORMAL;
                                }
                                indexField.indexName = resultSet.getString("index_name");
                                entity.indexFieldList.add(indexField);
                            }
                        }
                    }
                })
                .execute();

        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取索引字段信息")
                .putTemporaryData("sql", "select table_name, index_name, column_name from user_ind_columns")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("table_name");
                            String indexName = resultSet.getString("index_name");
                            String columnName = resultSet.getString("column_name");

                            for(Entity entity:entityList) {
                                if (!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }
                                for(IndexField indexField:entity.indexFieldList){
                                    if (!indexField.indexName.equalsIgnoreCase(indexName)) {
                                        continue;
                                    }
                                    indexField.columns.add(columnName);
                                }
                            }
                        }
                    }
                })
                .execute();
    }
}